package com.gpj.sqlbuilder.io;

import com.gpj.sqlbuilder.store.excel.CellInfo;
import com.gpj.sqlbuilder.store.excel.RowInfo;
import com.gpj.sqlbuilder.store.excel.Sheet;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;

/**
 * @className ExcelReader
 * @description
 *   读取Excel文件内容转存入缓存
 * @author GPJ
 * @date 2021/3/26 15:16
 * @version 1.0
 **/
@Slf4j
public class ExcelReader {
    private boolean rowEnd = false;
    /**
     * 读取Excel文件
     * @param path Excel文件路径
     */
    public Map<String, Sheet> read(String path){
        InputStream in = null;
        XSSFWorkbook workbook = null;
        try {
            File file = new File(path);
            in = new BufferedInputStream(new FileInputStream(file));
            ZipSecureFile.setMinInflateRatio(-1.0d);
            workbook = new XSSFWorkbook(in);
            return build(workbook);
        } catch (Exception e) {
            log.error("\texcel读取失败:{}", e.toString());
            if(log.isDebugEnabled()){
                log.error(e.getMessage(), e);
            }
        } finally {
            try {
                if(workbook != null){
                    workbook.close();
                }
                if(in != null){
                    in.close();
                }
            } catch (IOException e) {
                log.error("excel资源关闭失败:{}", e.toString(), e);
            }
        }
        return null;
    }

    /**
     * 解析Excel到内存
     * @param workbook Excel工作薄对象
     */
    private Map<String, Sheet> build(XSSFWorkbook workbook) {
        log.info("-- Excel解析 开始 --");
        if(workbook == null || workbook.getNumberOfSheets() < 1)
            log.error("工作薄无效");

        Map<String, Sheet> data = new HashMap<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            if(sheet != null && sheet.getLastRowNum() > 0){
                List<RowInfo> rows = new ArrayList<>();
                rowEnd = false;
                for (Row row : sheet) {
                    RowInfo rowInfo = rowBuild(sheet.getSheetName(), row);
                    if(rowInfo != null){
                        rows.add(rowInfo);
                    }
                    if(rowEnd){
                        break;
                    }
                }

                if(rows.size() > 0){
                    log.info("\t解析[sheet:{}] 成功", sheet.getSheetName());
                }else{
                    log.warn("\t解析[sheet:{}] 无行数据", sheet.getSheetName());
                }
                data.put(sheet.getSheetName(), new Sheet(sheet.getSheetName(), rows));
            }else{
                log.info("\t解析[sheet:] [错误:sheet对象为空]");
            }
        }
        log.info("-- Excel解析 结束 --");
        return data;
    }

    /**
     * 解析行数据,构建行对象
     * @param row POI行对象
     */
    private RowInfo rowBuild(String sheetName, Row row) {
        if(row != null && row.getRowNum() != 0){
            int rowNum = row.getRowNum();
            RowInfo rowInfo = new RowInfo(sheetName, rowNum);

            CellInfo cellInfo = cellBuild(sheetName, rowNum, 0, row.getCell(0));
            if(cellInfo != null && cellInfo.isValid()){
                List<CellInfo> cells = new ArrayList<>();

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    CellInfo cell = cellBuild(sheetName, rowNum, i, row.getCell(i));
                    if(cell.isEnd()) break;
                    log.debug("\t\t\t解析[sheet:{},row:{},cell:{}]", sheetName, rowNum, i);
                    cells.add(cell);
                }
                rowInfo.setCells(cells);
                if(cells != null && cells.size() > 0){
                    log.debug("\t\t解析[sheet:{},row:{}] [成功]", sheetName, rowNum);
                }else{
                    log.warn("\t\t解析[sheet:{},row:{}] [失败:无列数据]", sheetName, rowNum);
                }
                return rowInfo;
            }else{
                if(cellInfo.isMark()){
                    rowEnd = true;
                }
                log.trace("\t\t解析[sheet:{},row:{}] [非有效行]", sheetName, rowNum);
            }
        }else if(row == null){
            log.error("\t\t解析[sheet:{}] [错误:行对象为空]", sheetName);
        }
        return null;
    }

    /**
     * 解析单元格数据,构建单元格对象(不带日志)
     * @param sheetName sheet名称
     * @param rowNum 行号
     * @param cell POI单元格对象
     */
    private CellInfo cellBuild(String sheetName, int rowNum, int cellIndex, Cell cell) {
        if(cell != null) {
            return new CellInfo(sheetName, rowNum, cellValue(cell), cell.getColumnIndex());
        }
        return new CellInfo(sheetName, rowNum, null, cellIndex);
    }

    /**
     * 获取单元格中的数据，转为string类型
     * @param cell POI的单元格对象
     */
    private String cellValue(Cell cell){
        switch (cell.getCellTypeEnum()){
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return (int) cell.getNumericCellValue() + "";
            case BOOLEAN:
                return cell.getBooleanCellValue()?"1":"";
            default:
                return null;
        }
    }
}

